<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Database Access</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/Python - Python Procedural Language"
HREF="plpython.html"><LINK
REL="PREVIOUS"
TITLE="Trigger Functions"
HREF="plpython-trigger.html"><LINK
REL="NEXT"
TITLE="Explicit Subtransactions"
HREF="plpython-subtransaction.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Trigger Functions"
HREF="plpython-trigger.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpython.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 42. PL/Python - Python Procedural Language</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Explicit Subtransactions"
HREF="plpython-subtransaction.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPYTHON-DATABASE"
>42.7. Database Access</A
></H1
><P
>   The PL/Python language module automatically imports a Python module
   called <TT
CLASS="LITERAL"
>plpy</TT
>.  The functions and constants in
   this module are available to you in the Python code as
   <TT
CLASS="LITERAL"
>plpy.<TT
CLASS="REPLACEABLE"
><I
>foo</I
></TT
></TT
>.
  </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN56907"
>42.7.1. Database Access Functions</A
></H2
><P
>   The <TT
CLASS="LITERAL"
>plpy</TT
> module provides two
   functions called <CODE
CLASS="FUNCTION"
>execute</CODE
> and
   <CODE
CLASS="FUNCTION"
>prepare</CODE
>.  Calling
   <CODE
CLASS="FUNCTION"
>plpy.execute</CODE
> with a query string and an
   optional limit argument causes that query to be run and the result
   to be returned in a result object.  The result object emulates a
   list or dictionary object.  The result object can be accessed by
   row number and column name.  It has these additional methods:
   <CODE
CLASS="FUNCTION"
>nrows</CODE
> which returns the number of rows
   returned by the query, and <CODE
CLASS="FUNCTION"
>status</CODE
> which is the
   <CODE
CLASS="FUNCTION"
>SPI_execute()</CODE
> return value.  The result object
   can be modified.
  </P
><P
>   For example:
</P><PRE
CLASS="PROGRAMLISTING"
>rv = plpy.execute("SELECT * FROM my_table", 5)</PRE
><P>
   returns up to 5 rows from <TT
CLASS="LITERAL"
>my_table</TT
>.  If
   <TT
CLASS="LITERAL"
>my_table</TT
> has a column
   <TT
CLASS="LITERAL"
>my_column</TT
>, it would be accessed as:
</P><PRE
CLASS="PROGRAMLISTING"
>foo = rv[i]["my_column"]</PRE
><P>
  </P
><P
>   
   The second function, <CODE
CLASS="FUNCTION"
>plpy.prepare</CODE
>, prepares
   the execution plan for a query.  It is called with a query string
   and a list of parameter types, if you have parameter references in
   the query.  For example:
</P><PRE
CLASS="PROGRAMLISTING"
>plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])</PRE
><P>
   <TT
CLASS="LITERAL"
>text</TT
> is the type of the variable you will be
   passing for <TT
CLASS="LITERAL"
>$1</TT
>.  After preparing a statement, you
   use the function <CODE
CLASS="FUNCTION"
>plpy.execute</CODE
> to run it:
</P><PRE
CLASS="PROGRAMLISTING"
>rv = plpy.execute(plan, [ "name" ], 5)</PRE
><P>
   The third argument is the limit and is optional.
  </P
><P
>   Query parameters and result row fields are converted between
   PostgreSQL and Python data types as described
   in <A
HREF="plpython-data.html"
>Section 42.3</A
>.  The exception is that composite
   types are currently not supported: They will be rejected as query
   parameters and are converted to strings when appearing in a query
   result.  As a workaround for the latter problem, the query can
   sometimes be rewritten so that the composite type result appears as
   a result row rather than as a field of the result row.
   Alternatively, the resulting string could be parsed apart by hand,
   but this approach is not recommended because it is not
   future-proof.
  </P
><P
>   When you prepare a plan using the PL/Python module it is
   automatically saved.  Read the SPI documentation (<A
HREF="spi.html"
>Chapter 43</A
>) for a description of what this means.
   In order to make effective use of this across function calls
   one needs to use one of the persistent storage dictionaries
   <TT
CLASS="LITERAL"
>SD</TT
> or <TT
CLASS="LITERAL"
>GD</TT
> (see
   <A
HREF="plpython-sharing.html"
>Section 42.4</A
>). For example:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
    if SD.has_key("plan"):
        plan = SD["plan"]
    else:
        plan = plpy.prepare("SELECT 1")
        SD["plan"] = plan
    # rest of function
$$ LANGUAGE plpythonu;</PRE
><P>
  </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPYTHON-TRAPPING"
>42.7.2. Trapping Errors</A
></H2
><P
>    Functions accessing the database might encounter errors, which
    will cause them to abort and raise an exception.  Both
    <CODE
CLASS="FUNCTION"
>plpy.execute</CODE
> and
    <CODE
CLASS="FUNCTION"
>plpy.prepare</CODE
> can raise an instance of a subclass of
    <TT
CLASS="LITERAL"
>plpy.SPIError</TT
>, which by default will terminate
    the function.  This error can be handled just like any other
    Python exception, by using the <TT
CLASS="LITERAL"
>try/except</TT
>
    construct.  For example:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION try_adding_joe() RETURNS text AS $$
    try:
        plpy.execute("INSERT INTO users(username) VALUES ('joe')")
    except plpy.SPIError:
        return "something went wrong"
    else:
        return "Joe added"
$$ LANGUAGE plpythonu;</PRE
><P>
   </P
><P
>    The actual class of the exception being raised corresponds to the
    specific condition that caused the error.  Refer
    to <A
HREF="errcodes-appendix.html#ERRCODES-TABLE"
>Table A-1</A
> for a list of possible
    conditions.  The module
    <TT
CLASS="LITERAL"
>plpy.spiexceptions</TT
> defines an exception class
    for each <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> condition, deriving
    their names from the condition name.  For
    instance, <TT
CLASS="LITERAL"
>division_by_zero</TT
>
    becomes <TT
CLASS="LITERAL"
>DivisionByZero</TT
>, <TT
CLASS="LITERAL"
>unique_violation</TT
>
    becomes <TT
CLASS="LITERAL"
>UniqueViolation</TT
>, <TT
CLASS="LITERAL"
>fdw_error</TT
>
    becomes <TT
CLASS="LITERAL"
>FdwError</TT
>, and so on.  Each of these
    exception classes inherits from <TT
CLASS="LITERAL"
>SPIError</TT
>.  This
    separation makes it easier to handle specific errors, for
    instance:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
from plpy import spiexceptions
try:
    plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
    plpy.execute(plan, [numerator, denominator])
except spiexceptions.DivisionByZero:
    return "denominator cannot equal zero"
except spiexceptions.UniqueViolation:
    return "already have that fraction"
except plpy.SPIError, e:
    return "other error, SQLSTATE %s" % e.sqlstate
else:
    return "fraction inserted"
$$ LANGUAGE plpythonu;</PRE
><P>
    Note that because all exceptions from
    the <TT
CLASS="LITERAL"
>plpy.spiexceptions</TT
> module inherit
    from <TT
CLASS="LITERAL"
>SPIError</TT
>, an <TT
CLASS="LITERAL"
>except</TT
>
    clause handling it will catch any database access error.
   </P
><P
>    As an alternative way of handling different error conditions, you
    can catch the <TT
CLASS="LITERAL"
>SPIError</TT
> exception and determine
    the specific error condition inside the <TT
CLASS="LITERAL"
>except</TT
>
    block by looking at the <TT
CLASS="LITERAL"
>sqlstate</TT
> attribute of
    the exception object.  This attribute is a string value containing
    the <SPAN
CLASS="QUOTE"
>"SQLSTATE"</SPAN
> error code.  This approach provides
    approximately the same functionality
   </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpython-trigger.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpython-subtransaction.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Trigger Functions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plpython.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Explicit Subtransactions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>